package dao;


import exception.NotMatchException;
import model.Exam;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ExamTableDao {

    private ExamTableDao() {
    }

    public static List<Exam> getExams() throws SQLException {
        List<Exam> list = new ArrayList<>();

        String sql = "select student.s_no,student.s_name,score.c_name,score.ord_score,score.exam_score " +
                "from student,score " +
                "where student.s_no=score.s_no";
        Statement stmt = Dao.getConnection().createStatement();
        ResultSet rs = stmt.executeQuery(sql);

        while (rs.next()) {
            String sno = rs.getString(rs.findColumn("s_no"));
            String sname = rs.getString(rs.findColumn("s_name"));
            String cname = rs.getString(rs.findColumn("c_name"));
            int ordScore = rs.getInt(rs.findColumn("ord_score"));
            int examScore = rs.getInt(rs.findColumn("exam_score"));

            Exam exam = new Exam();
            exam.setSno(sno);
            exam.setSname(sname);
            exam.setCname(cname);
            exam.setOrdScore(ordScore);
            exam.setExamScore(examScore);
            list.add(exam);
        }

        return list;
    }

    public static void saveExams(List<Exam> exams) throws Exception {
        int rowIndex = 0;
        try {
            while (rowIndex < exams.size()) {
                Exam exam = exams.get(rowIndex);
                saveExam(exam);
                rowIndex++;
            }
        } catch (NotMatchException e) {
            throw new NotMatchException("第 " + rowIndex + " 行，" + e.getMessage());
        }
    }

    public static void saveExam(Exam exam) throws Exception {
        String sno = exam.getSno();
        String sname = exam.getSname();
        String cname = exam.getCname();
        int ordScore = exam.getOrdScore();
        int examScore = exam.getExamScore();
        int finalScore = exam.getFinalScore();

        Dao.checkMatch(sno, sname, cname);

        //查找cname对应的cno
        String sql1 = "select c_no from course where c_name=?";
        PreparedStatement stmt1 = Dao.getConnection().prepareStatement(sql1);
        stmt1.setString(1, cname);
        ResultSet rs = stmt1.executeQuery();

        String cno = null;
        if (rs.next()) {
            cno = rs.getString(rs.findColumn("c_no"));
        }

        //插入成绩
        String sql = "Insert " +
                "into score(s_no, c_no, c_name, ord_score, exam_score, final_score) " +
                "values(?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = Dao.getConnection().prepareStatement(sql);

        stmt.setString(1, sno);
        stmt.setString(2, cno);
        stmt.setString(3, cname);
        stmt.setInt(4, ordScore);
        stmt.setInt(5, examScore);
        stmt.setInt(6, finalScore);

        stmt.execute();
    }
}
